//
//  YWAttendanceDBManager.m
//  maintain
//
//  Created by kyo on 16/11/8.
//  Copyright © 2016年 ewin. All rights reserved.
//

#import "YWAttendanceDBManager.h"
#import "YWDBTool.h"
#import "YWReplyManager.h"

#import "YWAttendanceRecord.h"

@implementation YWAttendanceDBManager
YWSingletonM(Manager)

- (FMDatabase*)fmdb{
    return [[YWDBTool shareDBTool] fmdb];
}
- (FMDatabaseQueue*)dbQeue{
    return [[YWDBTool shareDBTool] dbQeue];
}

#pragma mark - 签到
/**
 *  名称 签到记录表 attendance_record
 */
- (BOOL)createAttendanceRecordTable
{
    NSString* sql = @"CREATE TABLE 'attendance_record' (recordId varchar(20) UNIQUE, arriveTime timestamp, arriveRemark varchar(200), leaveTime timestamp, leaveRemark varchar(200),yearMonth integer,dateShow varchar(20),type smallint,qrcodeId varchar,locationText varchar,coordinate varchar,uploadStatus integer,locationId integer,observerIds varchar);";
    return [[self fmdb] executeUpdate:sql];
}

- (BOOL)addColumInAttendanceRecord{
    __block BOOL executeResult = YES;
    if (![[self fmdb] columnExists:@"observerIds" inTableWithName:@"attendance_record"]) {
        [[self fmdb] executeUpdate:@"alter table attendance_record add column observerIds varchar;"];
    }
    return executeResult;
}

- (YWAttendanceRecord*)attendanceRecordWithSet:(FMResultSet*)set
 FMDatabase:(FMDatabase*)db{
    YWAttendanceRecord* attendanceRecord = [[YWAttendanceRecord alloc] init];
    attendanceRecord.recordId = [set stringForColumn:@"recordId"];
    attendanceRecord.arriveTime = [set doubleForColumn:@"arriveTime"];
    attendanceRecord.arriveRemark = [set stringForColumn:@"arriveRemark"];
    
    attendanceRecord.yearMonthDay = [set intForColumn:@"yearMonth"];
    attendanceRecord.dateShow = [set stringForColumn:@"dateShow"];
    attendanceRecord.type = [set intForColumn:@"type"];
    attendanceRecord.qrcodeId = [set stringForColumn:@"qrcodeId"];
    attendanceRecord.locationText = [set stringForColumn:@"locationText"];
    attendanceRecord.uploadStatus = [set intForColumn:@"uploadStatus"];
    attendanceRecord.coordinate = [set stringForColumn:@"coordinate"];
    attendanceRecord.locationId = [set intForColumn:@"locationId"];
    
    NSString* observerIds = [set stringForColumn:@"observerIds"];
    NSArray* idsArray = [observerIds componentsSeparatedByString:@","];
    NSMutableArray* observerArray = [NSMutableArray array];
    [idsArray enumerateObjectsUsingBlock:^(NSString* _Nonnull uniqueId, NSUInteger idx, BOOL* _Nonnull stop) {
        NSString* authorsql = @"SELECT * FROM 'handler' WHERE uniqueId =?;";
        FMResultSet* authorSet = [db executeQuery:authorsql, uniqueId];
        if ([authorSet next]) {
            YWHandler* handler = [[YWDBTool shareDBTool] handlerWithSet:authorSet];
            [observerArray addObject:handler];
        }
    }];
    attendanceRecord.observers = observerArray;
    return attendanceRecord;
}

- (YWAttendanceRecord*)newestAttendanceRecord
{
    __block YWAttendanceRecord* newestAttendanceRecord = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'attendance_record' order by arriveTime desc;";
        FMResultSet* set = [db executeQuery:sql];
        if ([set next]) {
            newestAttendanceRecord = [self attendanceRecordWithSet:set FMDatabase:db];
        }
        [db closeOpenResultSets];
    }];
    return newestAttendanceRecord;
}

- (NSArray*)attendanceRecordWithPage:(NSInteger)page
{
    __block NSInteger showCount = page * 10;
    __block NSMutableArray* allAttendanceRecord = [NSMutableArray array];
    __block NSString* sql = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        if (showCount == 0) {
            sql = @"SELECT * FROM 'attendance_record' order by yearMonth desc,arriveTime desc;";
        }
        else {
            sql = [NSString stringWithFormat:@"SELECT * FROM 'attendance_record' order by yearMonth desc,arriveTime desc LIMIT %ld;", (long)showCount];
        }
        
        FMResultSet* set = [db executeQuery:sql];
        while ([set next]) {
            YWAttendanceRecord* attendanceRecord = [self attendanceRecordWithSet:set FMDatabase:db];
            [allAttendanceRecord addObject:attendanceRecord];
        }
        [db closeOpenResultSets];
    }];
    return allAttendanceRecord;
}

- (NSArray*)attendanceRecordWithUploadStatusFail
{
    __block NSMutableArray* attendanceRecords = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'attendance_record' WHERE uploadStatus=? or uploadStatus=?";
        FMResultSet* set = [db executeQuery:sql, @(YWAttendanceUploadStatusFailure),@(YWAttendanceUploadStatusUploading)];
        while ([set next]) {
            YWAttendanceRecord* attendanceRecord = [self attendanceRecordWithSet:set FMDatabase:db];
            [attendanceRecords addObject:attendanceRecord];
        }
        [db closeOpenResultSets];
    }];
    return attendanceRecords;
}

- (BOOL)updateAttendanceUploadStatus:(NSInteger)uploadStatus withAttendanceRecordId:(NSString*)recordId
{
    __block BOOL executeResult = YES;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        sql = @"SELECT * FROM 'attendance_record' WHERE recordId=?;";
        FMResultSet* set = [db executeQuery:sql,recordId];
        if ([set next]) {
            sql = @"UPDATE 'attendance_record' SET uploadStatus =? WHERE recordId =?;";
            executeResult = [db executeUpdate:sql, @(uploadStatus), recordId];
        }
        if (!executeResult) {
            *rollback = YES;
        };
        
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (BOOL)updateAttendance:(YWAttendanceRecord*)attendance withRecordId:(NSString*)recordId
{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'attendance_record' WHERE recordId=?;";
        FMResultSet* set = [db executeQuery:sql, recordId];
        if ([set next]) {
            sql = @"UPDATE 'attendance_record' SET arriveTime=?,arriveRemark=?,yearMonth=?,dateShow=?,type=?,qrcodeId=?,locationText=?,uploadStatus=?,coordinate=?,recordId=?,locationId=?,observerIds=? WHERE recordId = ?;";
            executeResult = [db executeUpdate:sql, @(attendance.arriveTime), attendance.arriveRemark, @(attendance.yearMonthDay), attendance.dateShow,@(attendance.type),attendance.qrcodeId,attendance.locationText,@(attendance.uploadStatus),attendance.coordinate,attendance.recordId,@(attendance.locationId),attendance.observerIds,recordId];
        }
//        else {
//            sql = @"INSERT INTO 'attendance_record' (recordId,arriveTime,arriveRemark,yearMonth,dateShow,type,qrcodeId,locationText,coordinate,uploadStatus,locationId) VALUES (?, ?, ?,?,?,?,?,?,?,?,?);";
//            executeResult = [db executeUpdate:sql,attendance.recordId, @(attendance.arriveTime), attendance.arriveRemark, @(attendance.yearMonthDay), attendance.dateShow,@(attendance.type),attendance.qrcodeId,attendance.locationText,attendance.coordinate,@(attendance.uploadStatus),@(attendance.locationId)];
//        }
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (BOOL)updateAttendanceRecordWithRecord:(YWAttendanceRecord*)record
{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'attendance_record' WHERE recordId=?;";
        FMResultSet* set = [db executeQuery:sql, record.recordId];
        
        if ([set next]) {
            sql = @"UPDATE 'attendance_record' SET arriveTime=?,arriveRemark=?,yearMonth=?,dateShow=?,type=?,qrcodeId=?,locationText=?,uploadStatus=?,coordinate=?,locationId=?,observerIds=? WHERE recordId = ?;";
            executeResult = [db executeUpdate:sql, @(record.arriveTime), record.arriveRemark, @(record.yearMonthDay), record.dateShow,@(record.type),record.qrcodeId,record.locationText,@(record.uploadStatus),record.coordinate,@(record.locationId),record.observerIds,record.recordId];
        }
        else {
            sql = @"INSERT INTO 'attendance_record' (recordId,arriveTime,arriveRemark,yearMonth,dateShow,type,qrcodeId,locationText,coordinate,uploadStatus,locationId,observerIds) VALUES (?, ?, ?,?,?,?,?,?,?,?,?,?);";
            executeResult = [db executeUpdate:sql, record.recordId, @(record.arriveTime), record.arriveRemark, @(record.yearMonthDay), record.dateShow,@(record.type),record.qrcodeId,record.locationText,record.coordinate,@(record.uploadStatus),@(record.locationId),record.observerIds];
        }
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (BOOL)updateAttendanceRecordWithAttendRecordArray:(NSArray*)attendanceRecordArray{
    if (attendanceRecordArray.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSArray* blockRecordArray = attendanceRecordArray;
    __block NSMutableArray *replyArray = [NSMutableArray array];
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [blockRecordArray enumerateObjectsUsingBlock:^(YWAttendanceRecord *  _Nonnull record, NSUInteger idx, BOOL * _Nonnull stop) {
            [replyArray addObjectsFromArray:record.replies];//评论
            record.uploadStatus = YWAttendanceUploadStatusSucceed;
            sql = @"SELECT * FROM 'attendance_record' WHERE recordId=?;";
            FMResultSet* set = [db executeQuery:sql, record.recordId];
            if ([set next]) {
                sql = @"UPDATE 'attendance_record' SET arriveTime=?,arriveRemark=?,yearMonth=?,dateShow=?,type=?,qrcodeId=?,locationText=?,uploadStatus=?,coordinate=?,locationId=?,observerIds=? WHERE recordId = ?;";
                executeResult = [db executeUpdate:sql, @(record.arriveTime), record.arriveRemark, @(record.yearMonthDay), record.dateShow,@(record.type),record.qrcodeId,record.locationText,@(record.uploadStatus),record.coordinate,@(record.locationId),record.observerIds,record.recordId];
            }else {
                sql = @"INSERT INTO 'attendance_record' (recordId,arriveTime,arriveRemark,yearMonth,dateShow,type,qrcodeId,locationText,coordinate,uploadStatus,locationId,observerIds) VALUES (?, ?, ?,?,?,?,?,?,?,?,?,?);";
                executeResult = [db executeUpdate:sql, record.recordId, @(record.arriveTime), record.arriveRemark, @(record.yearMonthDay), record.dateShow,@(record.type),record.qrcodeId,record.locationText,record.coordinate,@(record.uploadStatus),@(record.locationId),record.observerIds];
            }    if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];
    //评论的更新
    [[YWReplyManager shareManager] updateReplyWithReplyArray:replyArray];
    return executeResult;
}
@end
